import os
import pyodbc
import pandas as pd
import Get_HTMLtables as GH
import numpy as np
from _sqlite3 import connect
import openpyxl
from openpyxl.reader.excel import load_workbook
global tables
import pyodbc
import re
import os
class PDFTableExtractor:
    def __init__(self, ID, find_keyword,Html_file):
        self.ID = ID
        read_html_obj = GH.ReadHtml()  # 创建GH.ReadHtml实例
        self.ZBDW=read_html_obj.read_DW(Html_file,find_keyword)
        try:
             self.BTSFXX=read_html_obj.read_BTSFXX(Html_file,find_keyword)
        except:
             self.BTSFXX='匹配不到，自己找一下'
    def get_EPBH(self):
        server = 'SHPUBSRV02'
        database = 'JYCFI'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        query = """
                   SELECT DISTINCT EPBH
                   FROM [10.106.22.51].JYPRIME.dbo.usrQYMB GG 
                   WHERE QYBH= ?
                       """
        IGSDM = str(self.IGSDM)
        self.EPBH = pd.read_sql(query, conn, params=[IGSDM])
        try:
            self.EPBH = self.EPBH['EPBH'][0]
        except:
            self.EPBH = 'EP未匹配成功，需要人工处理'

            return self.EPBH

    def get_GG(self):
        server = 'SHPUBSRV01'
        database = 'JYPRIME'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        ID = self.ID
        query = """
                                  SELECT ID,IGSDM,CONVERT(varchar(10),XXFBRQ,23) AS XXFBRQ,CONVERT(varchar(10),JZRQ,23) AS JZRQ,XXBT,GGLJ
                                  FROM usrGSGGYWFWB GG 
                                  WHERE ID= CAST(? AS NVARCHAR)
                                      """
        self.GG = pd.read_sql(query, conn, params=[ID])
        if not self.GG.empty:
            self.IGSDM = self.GG['IGSDM'][0]
            self.XXFBRQ = self.GG['XXFBRQ'][0]
            self.JZRQ = self.GG['JZRQ'][0]
            self.XXLY = self.GG['XXBT'][0]

            return self.GG
    def find_keyword_and_extract_table(self, Html_file, find_keyword):
        table = GH.ReadHtml.read_html_file(Html_file, find_keyword)
        if table.shape[1]!='0':
            table = table.replace('主要产品', 'SJLMSMC').replace(' ','',regex=True)
            try :
                if table[0][0] != '分行业情况' and table[0][0] == '分行业':
                    VALUE = ['分行业情况', '', '', '', '', '', '']
                    table.loc[-1] = VALUE
                    table.index = table.index + 1  # 更新索引
                    table = table.sort_index()  # 根据索引排序
                elif table[0][0] != '分产品情况' and table[0][0] == '分产品':
                    VALUE = ['分产品情况', '', '', '', '', '', '']
                    table.loc[-1] = VALUE
                    table.index = table.index + 1  # 更新索引
                    table = table.sort_index()  # 根据索引排序
            except:
                pass
            print()
        return table


    def xggs(self,table):
        new_excel_file_path = f'成本分析表/深交所/{self.IGSDM}_{self.JZRQ}_成本分析主表.xlsx'
        with pd.ExcelWriter(new_excel_file_path, engine='openpyxl') as writer:
                yysr=table
                new_columns = ['JYXMMCYS', 'CBGCXMYS', 'BQCBJE','BQCBZZCBBL','SNTQCBJE','SNTQCBZZCBBL','BQCBTBBD','BZSM']
                yysr = yysr.rename(columns=dict(zip(yysr.columns, new_columns)))
                yysr['FLFSMC']=None
                yysr['FLFS']=None
                yysr['IGSDM']=None
                yysr['IGSDM'].iloc[:len(yysr)]=self.IGSDM
                yysr['XXFBRQ']=None
                yysr['XXFBRQ'].iloc[:len(yysr)]=self.XXFBRQ
                yysr['JZRQ']=None
                yysr['JZRQ'].iloc[:len(yysr)]=self.JZRQ
                yysr['XXLY']=None
                yysr['XXLY'].iloc[:len(yysr)]=self.XXLY
                yysr['XXLYBM']=None
                yysr['XXLYBM'].iloc[:len(yysr)]='FCC00000005E'
                yysr['BZ']=None
                yysr['BZ'].iloc[:len(yysr)]='FCC000000015'
                yysr['ZBDW']=None
                yysr['ZBDW']=self.ZBDW
                yysr['YSHXH']=None

                yysr['BTSFXX']=None
                yysr['BTSFXX'].iloc[:len(yysr)]=self.BTSFXX
                if yysr.iloc[0][0]=='行业分类':
                    yysr['FLFSMC'].iloc[:len(yysr)]='按行业'
                    yysr['FLFS'].iloc[:len(yysr)]='10'
                elif yysr.iloc[0][0]=='产品分类':
                    yysr['FLFSMC'].iloc[:len(yysr)]='按产品'
                    yysr['FLFS'].iloc[:len(yysr)]='20'

                yysr = yysr.drop([0, 1], axis=0)
                yysr.reset_index(drop=True, inplace=True)
                yysr['YSHXH'] = range(1, len(yysr) + 1)
                yysr = yysr.reindex(
                    columns=['IGSDM', 'XXFBRQ', 'JZRQ', 'XXLY', 'XXLYBM', 'FLFS', 'FLFSMC', 'JYXMMCYS',
                             'CBGCXMYS', 'BQCBJE', 'ZBDW', 'BQCBZZCBBL','SNTQCBJE', 'SNTQCBZZCBBL', 'BQCBTBBD','YSHXH','BTSFXX', 'BZSM', 'BZ'])
                yysr.to_excel(writer, index=False, sheet_name=str(self.IGSDM))
                return yysr
def run_all(ID, Html_file):
    Html_file = Html_file
    find_keyword =[['行业分类','占营业成本比重'],['行业分类','本期占总成本比例']]
    extractor_instance = PDFTableExtractor(ID=ID, find_keyword=find_keyword, Html_file=Html_file)
    extractor_instance.get_GG()
    extractor_instance.get_EPBH()
    table1 = extractor_instance.find_keyword_and_extract_table(Html_file=Html_file, find_keyword=find_keyword)
    if table1.shape[1] != 0:
        table1 = extractor_instance.xggs(table1)
    find_keyword = [['成本构成项目', '产品分类'], ['本期占总成本比例', '产品分类'],['产品分类','占营业成本比重']]
    extractor_instance = PDFTableExtractor(ID=ID, find_keyword=find_keyword, Html_file=Html_file)
    extractor_instance.get_GG()
    extractor_instance.get_EPBH()
    table2 = extractor_instance.find_keyword_and_extract_table(Html_file=Html_file, find_keyword=find_keyword)
    if table2.shape[1] != 0:
        table2 = extractor_instance.xggs(table2)
    table = pd.concat([table1, table2])
    if table.shape[1] != 0:
        table=table.replace(' ','',regex=True).replace('%','',regex=True)
        table['YSHXH'] = None
        table['YSHXH'] = range(1, len(table) + 1)
        return table
    else:
        return pd.DataFrame()

if __name__ == '__main__':
    ID = '763764456002'
    Html_file = r'D:\软件打包\Juno-win32.win32.x86_64\Juno-win32.win32.x86_64\763764456002.HTML'
    run_all(ID, Html_file)